package org.tony.db;

import java.io.BufferedWriter;
import java.io.ByteArrayOutputStream;
import java.io.IOException;
import java.io.OutputStreamWriter;
import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.tony.entity.DbEntry;
import org.tony.utils.IdGenerator;
import org.tony.utils.JdbcHelper;
import org.tony.utils.TriggerTemplateInitial;
import org.tony.utils.TriggleTemplate;

import freemarker.template.Template;
import freemarker.template.TemplateException;

/**
 * 数据库操作 辅助类
 * @author tony
 *
 */
public class DbUtil {

	public static final String TIME_STR = "DATE|date|TIMESTAMP|timestamp|datetime|DATETIME";
	
	/**
	 * 生成查询表的sql语句
	 * @param entry
	 * @return
	 */
	private static String queryTableSql(DbEntry entry) {
		switch (DbType.valueOf(entry.getDatabaseType())) {
		case ORACLE:
			return "select table_name from user_tables" ;
		default:
			return null;
		}
	}
	
	/**
	 * 获取数据源中的所有源
	 * @param entry
	 * @return
	 */
	public static List<String> getAllTables(DbEntry entry) {
		List<String> tablesName = new ArrayList<String>();
		Connection con = JdbcHelper.getConnection(entry);
		String[] types = {"TABLE"} ;
		ResultSet res = null ;
		PreparedStatement pstmt = null ;
		try {
			String sql = queryTableSql(entry);
			if(sql != null) {
				pstmt = con.prepareStatement(sql) ;
				res = pstmt.executeQuery() ;
			}else {
				DatabaseMetaData metaData = con.getMetaData() ;
				res = metaData.getTables(null, null, null, types) ;
			}
			while(res.next()) {
				tablesName.add(res.getString("TABLE_NAME")) ;
			}
			return tablesName;
		} catch (SQLException e) {
			e.printStackTrace();
			return null ;
		} finally {
			JdbcHelper.closeResultSet(res);
			JdbcHelper.closeConnection(con);
		}
	};

	/**
	 * 获取数据源中 指定表的所有列
	 * @param tableName
	 * @param entry
	 * @param isSystem
	 * @return 返回 列名-类型 的map
	 */
	public static Map<String, String> getTableColumns(String tableName,
			DbEntry entry,boolean isSystem) {
		Map<String,String> tableInfomations = new HashMap<String, String>() ;
		Connection con = JdbcHelper.getConnection(entry);
		ResultSet res = null ;
		try {
			DatabaseMetaData metaData = con.getMetaData() ;
//			StringBuilder pk = new StringBuilder() ;
//			if(!isSystem) {
//				res = metaData.getPrimaryKeys(null, null, tableName) ;
//				while(res!=null && res.next()) {
//					pk.append(res.getObject("COLUMN_NAME") +"|") ;
//				}
//				System.out.println(pk);
//			}
//			JdbcHelper.closeResultSet(res);
			res = metaData.getColumns(null, "%", tableName, "%");
			while(res!= null && res.next()) {
				String columnName = res.getString("COLUMN_NAME").trim();
				String columnType = res.getString("TYPE_NAME").trim() ;
//				if(!isSystem) {
//					if(!pk.toString().contains(columnName)) {
//						tableInfomations.put(columnName, columnType);
//					}
//				}else {
//					tableInfomations.put(columnName, columnType);
//				}
				tableInfomations.put(columnName, columnType);
			}
			return tableInfomations;
		} catch (SQLException e) {
			e.printStackTrace();
			return null ;
		} finally {
			JdbcHelper.closeResultSet(res);
			JdbcHelper.closeConnection(con);
		}
	};
	
	/**
	 * 登陆
	 * @param username
	 * @param password
	 * @return
	 */
	public static String login(String username,String password) {
		Connection conn = JdbcHelper.getSystemDbConnection() ;
		String sql = "select * from users where username='"+username+"' and password='"+password+"'";
		Statement pstmt = null ;
		ResultSet rs = null ;
		try{
			if(conn != null) {
				pstmt = conn.createStatement();
				rs = pstmt.executeQuery(sql) ;
				while(rs.next()) {
					if(rs.getString("usertype").equals("1")) {
						return "admin/index.jsp" ;
					}else {
						return "user/index.jsp" ;
					}
				}
			}
		}catch (Exception e) {
			return null;
		}finally{
			JdbcHelper.closeResultSet(rs);
			JdbcHelper.closeStatement(pstmt) ;
			JdbcHelper.closeConnection(conn);
		}
		return null;
	}

	/**
	 * 创建触发器
	 * @param tableName
	 * @param columnNames
	 * @param entry
	 * @param relId
	 * @param relKey
	 * @return
	 */
	public static boolean createTrigger(String tableName, Map<String,String> columnNames,
			DbEntry entry,String relId,String relKey) {
		String templates = null ;
		switch (DbType.valueOf(entry.getDatabaseType())) {
			case ORACLE:
				templates = generateOracleTrigger(relKey,relId,tableName, columnNames, entry) ;
				System.out.println(templates);
				break;
			case MSSQL2005:
				try {
					templates = getTemplate(DbType.MSSQL2005,relId,relKey,tableName,columnNames,entry) ;
					System.out.println(templates);
				} catch (Exception e1) {
					e1.printStackTrace();
					return false ;
				}
				break;
			default:
				break;
		}
		if(templates!=null) {
			Connection con = null ;
			Statement stmt = null ;
			try{
				con = JdbcHelper.getConnection(entry) ;
				stmt = con.createStatement() ;
				stmt.executeUpdate(templates) ;
				return true;
			}catch (Exception e) {
				e.printStackTrace() ;
				return false ;
			} finally {
				JdbcHelper.closeStatement(stmt);
				JdbcHelper.closeConnection(con) ;
			}
		}else {
			return false ;
		}
		
	};
	
	/**
	 * 根据数据源获取触发器模板
	 * @param mssql2005
	 * @param relId
	 * @param relKey
	 * @param tableName
	 * @param columnNames
	 * @param entry
	 * @return
	 * @throws TemplateException
	 * @throws IOException
	 */
	private static String getTemplate(DbType mssql2005, String relId,String relKey,
			String tableName, Map<String, String> columnNames, DbEntry entry) throws TemplateException, IOException {
		Template tpl = TriggleTemplate.getInstance().getTemplate(mssql2005.name());
		Map<Object,Object> root = new HashMap<Object,Object> ();
		root.put("triggername", "t_"+IdGenerator.generateId()) ;
		root.put("tablename", tableName) ;
		root.put("relation_key_name", relKey) ;
		root.put("relation_id",relId) ;
		root.put("triggername", "t_"+IdGenerator.generateId()) ;
		root.put("cols", columnNames) ;
		ByteArrayOutputStream bos = new ByteArrayOutputStream(1024*1024) ;
		BufferedWriter bw = new BufferedWriter(new OutputStreamWriter(bos)) ;
		tpl.process(root, bw);
		return bos.toString().trim() ;
	}
	
	/**
	 * 获取oracle触发器模板
	 * @param relKey
	 * @param relId
	 * @param tableName
	 * @param columnNames
	 * @param entry
	 * @return
	 */
	public static String generateOracleTrigger(String relKey,String relId,String tableName, Map<String,String> columnNames,
			DbEntry entry) {
		String templates = TriggerTemplateInitial.getInstance().getTemplate(entry.getDatabaseType().trim()) ;
		StringBuilder insert_loops = new StringBuilder() ;
		StringBuilder update_loops = new StringBuilder() ;
		StringBuilder columns  = new StringBuilder();
		for(Iterator<String> colsIter = columnNames.keySet().iterator();colsIter.hasNext();) {
			String col = colsIter.next() ;
			String colType =columnNames.get(col).replaceAll("\\(\\d+\\)", "") ;
			if(TIME_STR.contains(colType)) {
				insert_loops.append("utl_submit_info.get_insert_node('"+col+"',to_char(:NEW."+col+",'yyyy/mm/dd hh:mi:ss'))");
				update_loops.append("utl_submit_info.get_insert_node('"+col+"',to_char(:NEW."+col+",'yyyy/mm/dd hh:mi:ss'))");
			}else {
				insert_loops.append("utl_submit_info.get_insert_node('"+col+"',to_char(:NEW."+col+"))");
				update_loops.append("utl_submit_info.get_insert_node('"+col+"',to_char(:NEW."+col+"))");
				
			}
			columns.append(col) ;
			if(colsIter.hasNext()) {
				insert_loops.append("||") ;
				update_loops.append("||") ;
				columns.append(",") ;
				
			}
		}
//		for(;i<columnNames.length-1;i++) {
//			columns.append(columnNames[i] + ",") ;
//			insert_loops.append("utl_submit_info.get_insert_node('"+columnNames[i]+"',to_char(:NEW."+columnNames[i]+"))||");
//			update_loops.append("utl_submit_info.get_update_node('"+columnNames[i]+"',to_char(:NEW."+columnNames[i]+"),to_char(:OLD."+columnNames[i]+"))||");
//			
//		}
//		columns.append(columnNames[i]) ;
//		insert_loops.append("utl_submit_info.get_insert_node('"+columnNames[i]+"',to_char(:NEW."+columnNames[i]+"))");
//		update_loops.append("utl_submit_info.get_update_node('"+columnNames[i]+"',to_char(:NEW."+columnNames[i]+"),to_char(:OLD."+columnNames[i]+"))");
		
		templates = templates.replaceAll("#rel_id", relId);
		templates = templates.replaceAll("#columns", columns.toString());
		templates = templates.replaceAll("#rel_key", relKey);
		templates = templates.replaceAll("#name", "t_"+org.tony.utils.IdGenerator.generateId());
		templates = templates.replaceAll("#tableName", tableName) ;
		templates = templates.replaceAll("#insert_loop", insert_loops.toString()) ;
		templates = templates.replaceAll("#update_loop", update_loops.toString()) ;
		templates = templates.replaceAll("##", "<") ;
		templates = templates.replaceAll("\\$", ">") ;
		return templates;
	}
	

	/**
	 * 执行sql语句
	 * @param sql
	 * @param entry
	 * @return
	 */
	public static boolean execute(String sql,DbEntry entry) {
		
		System.out.println(sql);
		
		Connection conn = null ;
		Statement stmt = null ;
		boolean result = false ;
		try{
			conn = JdbcHelper.getConnection(entry) ;
			stmt = conn.createStatement() ;
			result = stmt.execute(sql);
		}catch (Exception e) {
			e.printStackTrace() ;
		}finally{
			JdbcHelper.closeStatement(stmt);
			JdbcHelper.closeConnection(conn);
		}
		return result;
	}

}
